package hedron.db;

import hedron.db.model.Stock;
import hedron.db.model.StockGroup;
import hedron.db.model.StoreName;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class StockManager {
	final DBManager db;

	public StockManager(IDManager ID, DBManager db) {
		this.db = db;
	}

	public List<StockGroup> getStockGroups(String filter) {
		List<StockGroup> result = new ArrayList<StockGroup>();
		Connection conn = db.getConnection();
		if (conn != null) {
			try {
				StockGroup stockGroup = new StockGroup();
				StringBuilder sql = new StringBuilder();
				sql.append("select * from ");
				sql.append(stockGroup.getTableName());
				if (filter != null && filter.length() > 0) {
					sql.append(" WHERE ");
					sql.append(db.prepareSearchCondition("GROUP_NAME", filter));
				}
				sql.append(" ORDER BY GROUP_NAME");
				ResultSet rs = db.query(conn, sql.toString());
				if (rs != null) {
					while (db.next(rs)) {
						result.add(new StockGroup(rs));
					}
					db.close(rs);
				}
			} finally {
				db.closeConnection(conn);
			}
		}
		return result;
	}

	public ArrayList<Stock> getStocks(String filter, String sortColumn, String direction) {
		ArrayList<Stock> result = new ArrayList<Stock>();
		Connection conn = db.getConnection();
		if (conn != null) {
			try {
				if (sortColumn == null) {
					sortColumn = "STOCK_NAME";
				}
				if (direction == null) {
					direction = "ASC";
				}
				Stock stock = new Stock();
				StockGroup stockGroup = new StockGroup();
				StringBuilder sql = new StringBuilder();
				sql.append("select * from ");
				sql.append(stock.getTableName());
				sql.append(" s left outer join ");
				sql.append(stockGroup.getTableName());
				sql.append(" g ON s.GROUP_ID = g.GROUP_ID ");
				if (filter != null && filter.length() > 0) {
					sql.append(" WHERE ");
					sql.append(db.prepareSearchCondition("SEARCHTEXT", filter));
				}
				sql.append(" ORDER BY ");
				sql.append(sortColumn);
				sql.append(' ');
				sql.append(direction);
				ResultSet rs = db.query(conn, sql.toString());
				if (rs != null) {
					while (db.next(rs)) {
						result.add(new Stock(rs));
					}
					db.close(rs);
				}
			} finally {
				db.closeConnection(conn);
			}
		}
		return result;
	}

	public ArrayList<Stock> getGroupingStocks(String filter) {
		ArrayList<Stock> result = new ArrayList<Stock>();
		Connection conn = db.getConnection();
		if (conn != null) {
			try {
				Stock stock = new Stock();
				StringBuilder sql = new StringBuilder();
				sql.append("select * FROM ");
				sql.append(stock.getTableName());
				sql.append(" WHERE STOCK_ID in (select distinct GROUP_ID from HGROUPING )");
				if (filter != null && filter.length() > 0) {
					sql.append(" AND ");
					sql.append(db.prepareSearchCondition("SEARCHTEXT", filter));
				}
				sql.append(" ORDER BY STOCK_CODE");
				ResultSet rs = db.query(conn, sql.toString());
				if (rs != null) {
					while (db.next(rs)) {
						result.add(new Stock(rs));
					}
					db.close(rs);
				}
			} finally {
				db.closeConnection(conn);
			}
		}
		return result;
	}

	public ArrayList<Stock> getGroupedStocks(Stock groupedStock) {
		ArrayList<Stock> result = new ArrayList<Stock>();
		if (groupedStock != null) {
			Connection conn = db.getConnection();
			if (conn != null) {
				try {
					StringBuilder sql = new StringBuilder();
					sql.append("select * FROM HGROUPING g, ");
					sql.append(groupedStock.getTableName()).append(" s ");
					sql.append(" WHERE g.GROUP_ID = ").append(groupedStock.getID());
					sql.append(" AND g.STOCK_ID = s.STOCK_ID ");
					sql.append(" ORDER BY STOCK_NAME");
					ResultSet rs = db.query(conn, sql.toString());
					if (rs != null) {
						while (db.next(rs)) {
							result.add(new Stock(rs));
						}
						db.close(rs);
					}
				} finally {
					db.closeConnection(conn);
				}
			}
		}
		return result;
	}

	public ArrayList<Stock> updateGroupedStocks(Stock groupingStock, ArrayList<Stock> groupedStocks) {
		ArrayList<Stock> result = new ArrayList<Stock>();
		if (groupingStock != null) {
			Connection conn = db.getConnection();
			if (conn != null) {
				try {
					long groupingId = groupingStock.getID();
					db.ddlQuery(conn, "DELETE FROM HGROUPING WHERE GROUP_ID = " + groupingId);

					if (groupedStocks.size() > 0) {
						StringBuffer sql = new StringBuffer();
						ArrayList<Object> params = new ArrayList<Object>();
						for (Stock stock : groupedStocks) {
							if (stock.QTY > 0) {
								if (sql.length() == 0) {
									sql.append("INSERT INTO HGROUPING(GROUP_ID, STOCK_ID, QTY) VALUES (?,?,?)");
								} else {
									sql.append(",(?,?,?)");
								}
								params.add(groupingId);
								params.add(stock.getID());
								params.add(stock.QTY);
							}
						}
						db.ddlQuery(conn, sql.toString(), params.toArray());
					}
				} finally {
					db.closeConnection(conn);
				}
			}
		}
		return result;
	}
	

	public ArrayList<Stock> deleteGroupedStocksFor(Stock groupingStock) {
		ArrayList<Stock> result = new ArrayList<Stock>();
		if (groupingStock != null) {
			Connection conn = db.getConnection();
			if (conn != null) {
				try {
					long groupingId = groupingStock.getID();
					db.ddlQuery(conn, "DELETE FROM HGROUPING WHERE GROUP_ID = " + groupingId);
				} finally {
					db.closeConnection(conn);
				}
			}
		}
		return result;
	}

	public ArrayList<Stock> getAvailableGroupings(StoreName storeName) {
		ArrayList<Stock> result = new ArrayList<Stock>();

		Connection conn = db.getConnection();
		if (conn != null) {
			try {
				ResultSet rs = db.query(conn,
						"SELECT S.STOCK_ID, MAX(S.STOCK_NAME) as STOCK_NAME, FLOOR ( MIN (ST.QTY / G.QTY) ) as QTY FROM HSTOCK S, HGROUPING G,"
								+ "( SELECT STOCK_ID , SUM(QTY) AS QTY  FROM HSTORE WHERE STORE_ID = ? GROUP BY STOCK_ID) ST"
								+ " WHERE S.STOCK_ID = G.GROUP_ID AND G.STOCK_ID = ST.STOCK_ID "
								+ " GROUP BY S.STOCK_ID ORDER BY S.STOCK_NAME", new Long(storeName.getID()));
				if (rs != null) {
					while (db.next(rs)) {
						final Stock stock = new Stock(rs);
						result.add(stock);
					}
					db.close(rs);
				}
			} finally {
				db.closeConnection(conn);
			}
		}

		return result;
	}
}
